<?php

namespace app\admin\service;

use app\api\service\WechatService;
use app\model\PayRecord;
use app\model\WithdrawOrderRecords;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Db;
use app\model\Videos;
use app\model\Applet;
use app\common\Common;
use app\model\User;
use app\model\DistributionSettlement;
use app\common\Error;
use think\helper\Arr;

// 财务服务类
class FinanceService
{
    private $model;
    private $videoModel;
    private $appletModel;
    private $userModel;
    private $distributionSettlementModel;
    private $withdrawOrderRecordsModel;

    public function __construct(PayRecord $model, Videos $videoModel, Applet $appletModel, User $userModel, DistributionSettlement $distributionSettlementModel,WithdrawOrderRecords $withdrawOrderRecordsModel)
    {
        $this->model = $model;
        $this->videoModel = $videoModel;
        $this->appletModel = $appletModel;
        $this->userModel = $userModel;
        $this->distributionSettlementModel = $distributionSettlementModel;
        $this->withdrawOrderRecordsModel = $withdrawOrderRecordsModel;
    }

    // 获取充值统计数据
    public function getRechargeStatisticsData($params)
    {
        $type = $params['type'];

        $where[] = ['status', '=', 1];
        // $where[] = ['type', 'in', [1, 2, 3, 4, 5, 6, 7, 8, 11]];
        $where[] = ['total_fee', '>', 0];

        if (!empty($params['start_date'])) {
            $where[] = ['consume_date', '>=', str_replace('-', '', $params['start_date'])];
        }

        if (!empty($params['end_date'])) {
            $where[] = ['consume_date', '<=', str_replace('-', '', $params['end_date'])];
        }

        if (!empty($params['start_month'])) {
            $where[] = ['consume_month', '>=', str_replace('-', '', $params['start_month'])];
        }

        if (!empty($params['end_month'])) {
            $where[] = ['consume_month', '<=', str_replace('-', '', $params['end_month'])];
        }

        if (isset($params['date']) && is_numeric($params['date'])) {
            if ($params['date'] == 0) {
                // 今天
                $where[] = ['consume_date', '=', date('Ymd')];
            }
            if ($params['date'] == 1) {
                // 昨天
                $where[] = ['consume_date', '=', date('Ymd', time() - 86400)];
            }
            if ($params['date'] == 7) {
                // 近7天
                $where[] = ['consume_date', '>=', date('Ymd', time() - 86400*7)];
                $where[] = ['consume_date', '<', date('Ymd')];
            }
            if ($params['date'] == 30) {
                // 近30天
                $where[] = ['consume_date', '>=', date('Ymd', time() - 86400*30)];
                $where[] = ['consume_date', '<', date('Ymd')];
            }
        }

        if (!empty($params['month'])) {
            if ($params['month'] == 1) {
                $startDate = date('Ym', strtotime('-1 month')) . '01';
                $endDate = date('Ymt', strtotime(-date('d').'day'));
                $where[] = ['consume_date', '>=', $startDate];
                $where[] = ['consume_date', '<=', $endDate];
            }

            if ($params['month'] == 3) {
                $startDate = date('Ym', strtotime('-3 month')) . '01';
                $endDate = date('Ymt', strtotime(-date('d').'day'));
                $where[] = ['consume_date', '>=', $startDate];
                $where[] = ['consume_date', '<=', $endDate];
            }
        }

        if ($type == 1 || $type == 2) {
            $totalRecharge = Db::name('pay_record')->where($where)->sum('total_fee'); // 总充值

            $wechatRecharge = Db::name('pay_record')->where($where)->where('pay_type', 1)->sum('total_fee'); // 微信充值

            $alipayRecharge = Db::name('pay_record')->where($where)->where('pay_type', 2)->sum('total_fee'); // 支付宝充值

            $rechargeUserNum = Db::name('pay_record')->where($where)->group('uid')->count(); // 充值人数

            $rechargeNum = Db::name('pay_record')->where($where)->count(); // 充值次数

            return [
                '总充值' => $totalRecharge,
                '微信充值' => $wechatRecharge,
                '支付宝充值' => $alipayRecharge,
                '充值人数' => $rechargeUserNum,
                '充值次数' => $rechargeNum
            ];
        } else if ($type == 3 || $type == 4) {
            $totalRecharge = Db::name('pay_record')->where($where)->sum('total_fee'); // 总充值

            $wechatAppletRecharge = Db::name('pay_record')->where($where)->where('source', 1)->sum('total_fee'); // 微信小程序充值

            $fastAppRecharge = Db::name('pay_record')->where($where)->where('source', 5)->sum('total_fee'); // 快应用充值

            $h5Recharge = Db::name('pay_record')->where($where)->where('source', 4)->sum('total_fee'); // H5充值

            $tiktokAppletRecharge = Db::name('pay_record')->where($where)->where('source', 2)->sum('total_fee'); // 抖音小程序

            $kwaiAppletRecharge = Db::name('pay_record')->where($where)->where('source', 3)->sum('total_fee'); // 快手小程序

            return [
                '总充值' => $totalRecharge,
                '微信小程序充值' => $wechatAppletRecharge,
                '快应用充值' => $fastAppRecharge,
                'H5充值' => $h5Recharge,
                '抖音小程序充值' => $tiktokAppletRecharge,
                '快手小程序充值' => $kwaiAppletRecharge
            ];
        }
    }

    // 获取充值报表数据
    public function getFinanceRechargeList($params)
    {
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 10;
        $type = $params['type'];

        $where[] = ['total_fee', '>', 0];
        $where[] = ['status', '=', 1];

        if (!empty($params['start_date'])) {
            $where[] = ['consume_date', '>=', str_replace('-', '', $params['start_date'])];
        }

        if (!empty($params['end_date'])) {
            $where[] = ['consume_date', '<=', str_replace('-', '', $params['end_date'])];
        }

        if (!empty($params['start_month'])) {
            $where[] = ['consume_month', '>=', str_replace('-', '', $params['start_month'])];
        }

        if (!empty($params['end_month'])) {
            $where[] = ['consume_month', '<=', str_replace('-', '', $params['end_month'])];
        }

        if (isset($params['date']) && is_numeric($params['date'])) {
            if ($params['date'] == 0) {
                // 今天
                $where[] = ['consume_date', '=', date('Ymd')];
            }
            if ($params['date'] == 1) {
                // 昨天
                $where[] = ['consume_date', '=', date('Ymd', time() - 86400)];
            }
            if ($params['date'] == 7) {
                // 近7天
                $where[] = ['consume_date', '>=', date('Ymd', time() - 86400*7)];
                $where[] = ['consume_date', '<', date('Ymd')];
            }
            if ($params['date'] == 30) {
                // 近30天
                $where[] = ['consume_date', '>=', date('Ymd', time() - 86400*30)];
                $where[] = ['consume_date', '<', date('Ymd')];
            }
        }

        if (!empty($params['month'])) {
            if ($params['month'] == 1) {
                $consumeMonth = date('Ym', strtotime('-1 month'));
                $where[] = ['consume_month', '=', $consumeMonth];
            }

            if ($params['month'] == 3) {
                $startMonth = date('Ym', strtotime('-3 month'));
                $endMonth = date('Ym', strtotime(-date('d').'day'));
                $where[] = ['consume_month', '>=', $startMonth];
                $where[] = ['consume_month', '<=', $endMonth];
            }
        }

        $return = ['total' => 0, 'total_page' => 0, 'data' => []];
        $totalAndPagingData = $this->getTotalAndPagingData($params);

        $offset = ($page - 1) * $limit;
        $totalPage = ceil($totalAndPagingData['total'] / $limit);
        if ($params['page'] > $totalPage) {
            return $return;
        }

        if ($type == 1) {
            // 充值渠道日报表
            $model = Db::name('pay_record')->field('consume_date, pay_type, source, total_fee, uid')->where($where);
            if (!empty($totalAndPagingData['date'])) {
                $model->where(['consume_date' => $totalAndPagingData['date']]);
            }
            $data = $model->order('consume_date desc')->select()->toArray();

            $result = [];
            foreach ($data as $key => $value) {
                $result[$value['consume_date']][] = $value['total_fee'];
                $result[$value['consume_date'].'_'.$value['pay_type']][] = $value['total_fee'];
                $result[$value['consume_date'].'_uid'][] = $value['uid'];
            }

            $returnData = [];
            foreach ($totalAndPagingData['date'] as $value) {

                $returnData[] = [
                    'date' => substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2),
                    'total_recharge' => empty($result[$value]) ? 0 : round(array_sum($result[$value]), 2),
                    'wechat_recharge' => empty($result[$value.'_1']) ? 0 : round(array_sum($result[$value.'_1']), 2),
                    'alipay_recharge' => empty($result[$value.'_2']) ? 0 : round(array_sum($result[$value.'_2']), 2),
                    'recharge_user_num' => empty($result[$value.'_uid']) ? 0 : count(array_unique($result[$value.'_uid'])),
                    'recharge_num' => empty($result[$value]) ? 0 : count($result[$value])
                ];
            }

        } else if ($type == 2) {
            // 充值渠道月报表
            $model = Db::name('pay_record')->field('consume_month, pay_type, source, total_fee, uid')->where($where);
            if (!empty($totalAndPagingData['month'])) {
                $model->where(['consume_month' => $totalAndPagingData['month']]);
            }
            $data = $model->order('consume_month desc')->select()->toArray();

            $result = [];
            foreach ($data as $key => $value) {
                $result[$value['consume_month']][] = $value['total_fee'];
                $result[$value['consume_month'].'_'.$value['pay_type']][] = $value['total_fee'];
                $result[$value['consume_month'].'_uid'][] = $value['uid'];
            }

            $returnData = [];
            foreach ($totalAndPagingData['month'] as $value) {

                $returnData[] = [
                    'month' => substr($value, 0, 4) . '-' . substr($value, 4, 2),
                    'total_recharge' => empty($result[$value]) ? 0 : round(array_sum($result[$value]), 2),
                    'wechat_recharge' => empty($result[$value.'_1']) ? 0 : round(array_sum($result[$value.'_1']), 2),
                    'alipay_recharge' => empty($result[$value.'_2']) ? 0 : round(array_sum($result[$value.'_2']), 2),
                    'recharge_user_num' => empty($result[$value.'_uid']) ? 0 : count(array_unique($result[$value.'_uid'])),
                    'recharge_num' => empty($result[$value]) ? 0 : count($result[$value])
                ];
            }

        } else if ($type == 3) {
            // 产品来源日报表
            $model = Db::name('pay_record')->field('consume_date, pay_type, source, total_fee, uid')->where($where);
            if (!empty($totalAndPagingData['date'])) {
                $model->where(['consume_date' => $totalAndPagingData['date']]);
            }
            $data = $model->order('consume_date desc')->select()->toArray();

            $result = [];
            foreach ($data as $key => $value) {
                $result[$value['consume_date']][] = $value['total_fee'];
                $result[$value['consume_date'].'_'.$value['source']][] = $value['total_fee'];
                $result[$value['consume_date'].'_uid'][] = $value['uid'];
            }

            $returnData = [];
            foreach ($totalAndPagingData['date'] as $value) {

                $returnData[] = [
                    'date' => substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2),
                    'total_recharge' => empty($result[$value]) ? 0 : round(array_sum($result[$value]), 2),
                    'wechat_applet_recharge' => empty($result[$value.'_1']) ? 0 : round(array_sum($result[$value.'_1']), 2),
                    'fast_app_recharge' => empty($result[$value.'_5']) ? 0 : round(array_sum($result[$value.'_5']), 2),
                    'h5_recharge' => empty($result[$value.'_4']) ? 0 : round(array_sum($result[$value.'_4']), 2),
                    'tiktok_applet_recharge' => empty($result[$value.'_2']) ? 0 : round(array_sum($result[$value.'_2']), 2),
                    'kwai_applet_recharge' => empty($result[$value.'_3']) ? 0 : round(array_sum($result[$value.'_3']), 2)
                ];
            }

        } else if ($type == 4) {
            // 产品来源月报表
            $model = Db::name('pay_record')->field('consume_month, pay_type, source, total_fee, uid')->where($where);
            if (!empty($totalAndPagingData['month'])) {
                $model->where(['consume_month' => $totalAndPagingData['month']]);
            }
            $data = $model->order('consume_month desc')->select()->toArray();

            $result = [];
            foreach ($data as $key => $value) {
                $result[$value['consume_month']][] = $value['total_fee'];
                $result[$value['consume_month'].'_'.$value['source']][] = $value['total_fee'];
                $result[$value['consume_month'].'_uid'][] = $value['uid'];
            }

            $returnData = [];
            foreach ($totalAndPagingData['month'] as $value) {

                $returnData[] = [
                    'month' => substr($value, 0, 4) . '-' . substr($value, 4, 2),
                    'total_recharge' => empty($result[$value]) ? 0 : round(array_sum($result[$value]), 2),
                    'wechat_applet_recharge' => empty($result[$value.'_1']) ? 0 : round(array_sum($result[$value.'_1']), 2),
                    'fast_app_recharge' => empty($result[$value.'_5']) ? 0 : round(array_sum($result[$value.'_5']), 2),
                    'h5_recharge' => empty($result[$value.'_4']) ? 0 : round(array_sum($result[$value.'_4']), 2),
                    'tiktok_applet_recharge' => empty($result[$value.'_2']) ? 0 : round(array_sum($result[$value.'_2']), 2),
                    'kwai_applet_recharge' => empty($result[$value.'_3']) ? 0 : round(array_sum($result[$value.'_3']), 2)
                ];
            }

        }

        return ['total' => $totalAndPagingData['total'], 'total_page' => $totalPage, 'data' => $returnData];
    }

    // 获取总数
    public function getTotalAndPagingData($params)
    {
        $startDate = '2023-11-16';
        $startMonth = '2023-11';
        if (isset($params['date']) && is_numeric($params['date'])) {
            if ($params['date'] == 0) {
                // 今天
                $total = 1;
                $date[] = date('Ymd');
                return ['total' => $total, 'date' => $date];
            }
            if ($params['date'] == 1) {
                // 昨天
                $total = 1;
                $date[] = date('Ymd', time() - 86400);
            }
            if ($params['date'] == 7) {
                // 近7天
                $total = 7;
                for ($i = 1; $i <= 7; $i++) {
                    $date[] = date('Ymd', time() - $i * 86400);
                }
            }
            if ($params['date'] == 30) {
                // 近30天
                $total = 30;

                $offset = ($params['page'] - 1) * $params['limit'];
                $max = $params['page'] * $params['limit'] < $total ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : $total;
                $date = [];
                for ($i = $offset; $i < $max; $i++) {
                    $date[] = date('Ymd', time() - 86400 - $i * 86400);
                }
            }
        }

        if (!empty($params['month'])) {
            if ($params['month'] == 1) {
                $total = 1;
                $month[] = date('Ym', strtotime('-1 month'));
            }


            if ($params['month'] == 3) {
                $total = 3;
                $month = [];
                for ($i = 1; $i <= 3; $i++) {
                    $month[] = date('Ym', time() - $i * 30 *  86400);
                }
            }
        }

        if (!empty($params['start_date']) && !empty($params['end_date'])) {
            $total = (strtotime($params['end_date']) - strtotime($params['start_date'])) / 86400 + 1;

            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < $total ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : $total;
            $date = [];
            for ($i = $offset; $i < $max; $i++) {
                $date[] = date('Ymd', strtotime($params['end_date']) - $i * 86400);
            }
            // dump($date);die;
        } else if (!empty($params['start_date'])) {
            $total = (strtotime(date('Y-m-d')) - strtotime($params['start_date'])) / 86400 + 1;
            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < $total ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : $total;
            $date = [];
            for ($i = $offset; $i < $max; $i++) {
                $date[] = date('Ymd', time() - $i * 86400);
            }

        } else if (!empty($params['end_date'])) {
            $total = (strtotime($params['end_date']) - strtotime($startDate)) / 86400 + 1;
            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < $total ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : $total;
            $date = [];
            for ($i = $offset; $i < $max; $i++) {
                $date[] = date('Ymd', strtotime($params['end_date']) - $i * 86400);
            }

        }

        if (!empty($params['start_month']) && !empty($params['end_month'])) {
            $total = $this->getMonthForDates($params['start_month'] . '-01', $params['end_month'] . '-01');
            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < count($total) ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : count($total);
            $month = [];
            for ($i = $offset; $i < $max; $i++) {
                // $month[] = date('Ym', strtotime($params['end_month']) - $i * 86400 * 30);
                $month[] = $total[$i];
            }
            $total = count($total);
        } else if (empty($params['start_month']) && !empty($params['end_month'])) {
            $total = $this->getMonthForDates($startDate, $params['end_month'] . '-01');
            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < count($total) ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : count($total);
            $month = [];
            for ($i = $offset; $i < $max; $i++) {
                // $month[] = date('Ym', strtotime($params['end_month']) - $i * 86400 * 30);
                $month[] = $total[$i];
            }
            $total = count($total);
        } else if (!empty($params['start_month']) && empty($params['end_month'])) {
            $total = $this->getMonthForDates($params['start_month'] . '-01', date('Y-m') . '-01');
            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < count($total) ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : count($total);
            $month = [];
            for ($i = $offset; $i < $max; $i++) {
                // $month[] = date('Ym', strtotime(date('Y-m')) - $i * 86400 * 30);
                $month[] = $total[$i];
            }
            $total = count($total);
        }

        if ($params['date_type'] == 1 && empty($params['date']) && empty($params['start_date']) && empty($params['end_date'])) {
            $total = (strtotime(date('Y-m-d')) - strtotime($startDate)) / 86400 + 1;

            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < $total ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : $total;
            $date = [];
            for ($i = $offset; $i < $max; $i++) {
                $date[] = date('Ymd', time() - $i * 86400);
            }

        }

        if ($params['date_type'] == 2 && empty($params['month']) && empty($params['start_month']) && empty($params['end_month'])) {
            $total = $this->getMonthForDates($startDate, date('Y-m-d'));

            $offset = ($params['page'] - 1) * $params['limit'];
            $max = $params['page'] * $params['limit'] < count($total) ? $params['limit'] + ($params['page'] - 1) * $params['limit'] : count($total);
            $month = [];
            for ($i = $offset; $i < $max; $i++) {
                // $month[] = date('Ym', strtotime(date('Y-m-d')) - $i * 86400 * 30);
                $month[] = $total[$i];
            }
            $total = count($total);
        }

        if (!empty($date) && empty($month)) {
            return ['total' => $total, 'date' => $date];
        } else if (empty($date) && !empty($month)) {
            return ['total' => $total, 'month' => $month];
        } else {
            return ['total' => $total];
        }
    }

    // 获得两个日期之间的月份
    function getMonthForDates($sDate, $eDate)
    {
        $sTime = strtotime(date('Y-m-01', strtotime($sDate)));
        $eTime = strtotime(date('Y-m-01', strtotime($eDate)));

        $months = [];
        while ($sTime <= $eTime) {
            $months[] = date('Ym', $sTime);
            $sTime = strtotime('next month', $sTime);
        }
        return array_reverse($months);
    }

    // 获得两个日期之间的月数
    function getMonNums($begin, $end)
    {
        $nums = 0;
        $b = explode('-', $begin);
        $e = explode('-', $end);

        $years = $e[0] - $b[0];

        if (!$years) {
            // 年份相等
            $nums = $e[1] - $b[1] + 1;
        } else {
            if ($years == 1) {
                // 一年内, 开始与12月之间的月份 + 1, 再加结束年的月份
                $nums = 12 - $b[1] + 1 + $e[1];
            } else {
                // 超过一年的, 需要差的年份-1 乘以 12
                $nums = ($years - 1) * 12 + 12 - $b[1] + 1 + $e[1];
            }
        }
        return $nums;
    }

    // 快手小程序订单列表
    public function getKwaiAppletOrderList($params)
    {
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 10;
        $where[] = ['source', '=', 3];
        $where[] = ['status', '=', 1];

        // 日期搜索
        if (!empty($params['start_date'])) {
            $where[] = ['pay_time', '>=', strtotime($params['start_date'])];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['pay_time', '<', strtotime($params['end_date'].' 23:59:59')];
        }


        if (isset($params['is_settlement']) && is_numeric($params['is_settlement'])) {
            $where[] = ['is_settlement', '=', $params['is_settlement']];
        }
        $data = $this->model->getPagingDataByWhere($where, $page, $limit, 'id, video_id, uid, order_no, wechat_order_no, platform_order_no, total_fee, pay_type, pay_time, created_at, is_settlement');

        if ($data['data']) {

            // 获取短剧
            $videoIds = array_column($data['data'], 'video_id');
            $videoData = $this->videoModel->getDataByIds($videoIds, 'id, name, cover_img');
            $video = [];
            foreach ($videoData as $key => $value) {
                $video[$value['id']] = $value['name'];
            }

            // 是否同步/结算
            $payIds = array_column($data['data'], 'id');
            $syncSettlementData = Db::name('kwai_order_sync_settlement')->where(['pay_id' => $payIds])->field('pay_id, sync_status, settlement_status, settlement_error_msg')->select()->toArray();
            $syncSettlementStatus = array_column($syncSettlementData, NULL, 'pay_id');

            foreach ($data['data'] as $key => $item) {
                $data['data'][$key]['video_name'] = empty($video[$item['video_id']]) ? '' : $video[$item['video_id']];
                if ($item['is_settlement'] == 1) {
                    $data['data'][$key]['sync_status'] = 1;
                } else {
                    $data['data'][$key]['sync_status'] = empty($syncSettlementStatus[$item['id']]) ? 0 : $syncSettlementStatus[$item['id']]['sync_status'];
                    $data['data'][$key]['is_settlement'] = empty($syncSettlementStatus[$item['id']]) ? 0 : $syncSettlementStatus[$item['id']]['settlement_status'];
                    $data['data'][$key]['settlement_error_msg'] = empty($syncSettlementStatus[$item['id']]) ? '' : $syncSettlementStatus[$item['id']]['settlement_error_msg'];
                }
            }
        }

        // var_dump($data);exit;
        return $data;
    }

    // 签名
    public function sign($body = [], $appletData = [])
    {
        $body['app_id'] = $appletData['app_id'];
        ksort($body);
        $string = '';
        foreach ($body as $key => $value) {
            $string .= $key.'='.$value.'&';
        }
        $string = substr($string, 0, strlen($string) - 1).$appletData['app_secret'];
        return md5($string);
    }

    // 统计快手已支付订单金额
    public function getKwaiStatisticsPrice($params)
    {
        $where[] = ['source', '=', 3];
        $where[] = ['status', '=', 1];
        if (isset($params['is_settlement']) && is_numeric($params['is_settlement'])) {
            $where[] = ['is_settlement', '=', $params['is_settlement']];
        }
        // 日期搜索
        if (!empty($params['start_date'])) {
            $where[] = ['pay_time', '>=', strtotime($params['start_date'])];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['pay_time', '<', strtotime($params['end_date'].' 23:59:59')];
        }
        $totalPrice = $this->model->getSumPrice($where);
        return ['total_price' => $totalPrice];
    }

    // 快手订单结算
    public function kwaiOrderBatchSettlement($params)
    {
        $where[] = ['app_id', 'EXP', Db::raw('IS NOT NULL')];
        $where[] = ['sync_status', '=', 1];
        $where[] = ['settlement_status', '=', 0];
        // $where[] = ['created_at', '<', 1704495000];
        $where[] = ['created_at', '<', time() - 86400 * 3];
        $orderData = Db::name('kwai_order_sync_settlement')->where($where)->field('id, uid, order_no, pay_id, created_at, app_id')->select()->toArray();
        // dump($orderData);die;
        $paySaveData = $settlementData = [];

        foreach ($orderData as $key => $value) {
            if ($value['app_id']) {
                $appletData = $this->appletModel->getInfoByWhere(['app_id' => $value['app_id']], 'app_id, app_secret');
                $accessToken = Common::getKwaiAppletAccessToken($appletData['app_id'], $appletData['app_secret']);
                $url = 'https://open.kuaishou.com/openapi/mp/developer/epay/settle?app_id='.$appletData['app_id'].'&access_token='.$accessToken;

                $settleNo = date('YmdHis') . rand(1000, 10000);

                $requestData = [
                    'out_order_no' => $value['order_no'],
                    'out_settle_no' => $settleNo,
                    'reason' => '已支付订单结算',
                    'notify_url' => 'https://' . config('app.app_host') . '/admin/finance/settlementCallback'
                ];

                $sign = self::sign($requestData, $appletData);
                $requestData['sign'] = $sign;

                $prepayData = Common::curlRequest($url, 'POST', json_encode($requestData), ['Content-Type: application/json']);

                $result = json_decode($prepayData, true);

                if (isset($result['result']) && $result['result'] == 1) {

                    $paySaveData[] = [
                        'id' => $value['pay_id'],
                        'is_settlement' => 1,
                        'updated_at' => time()
                    ];
                    Db::name('kwai_order_sync_settlement')->where(['order_no' => $value['order_no']])->update([
                        'settlement_status' => 1,
                        'settle_no' => $result['settle_no'],
                        'updated_at' => time()
                    ]);

                } else {

                    Db::name('kwai_order_sync_settlement')->where(['order_no' => $value['order_no']])->update([
                        'settlement_status' => 2,
                        'settlement_error_msg' => $result['error_msg'],
                        'settle_no' => $settleNo,
                        'updated_at' => time()
                    ]);

                }
            }
        }

        $bool = $this->model->saveAll($paySaveData);

        return ['code' => 1, 'message' => '结算成功'];
    }

    // 分销商待处理结算列表
    public function getDistributionSettlementList($params)
    {
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 10;
        $where = [];

        // 公司搜索
        if (!empty($params['company_id'])) {
            $where[] = ['rp.company_id', '=', $params['company_id']];
        }

        // 分销商搜索
        if (!empty($params['distributor_id'])) {
            $where[] = ['distributor_id', '=', $params['distributor_id']];
        } else {
            $where[] = ['distributor_id', 'EXP', Db::raw('IS NOT NULL')];
        }
        //结算状态
        if (!empty($params['status'])) {
            $where[] = ['status', '=', $params['status']];
        }

       // 结算日期搜索
        if (!empty($params['start_date'])) {
            $where[] = ['start_date', '>=', date('Ymd', strtotime($params['start_date']))];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['end_date', '<=', date('Ymd', strtotime($params['end_date']))];
        }
        $data = $this->distributionSettlementModel->getPagingDataByWhereAdmin($where, $page, $limit, '`ds`.id, `ds`.name, `ds`.start_date, `ds`.end_date, `ds`.settlement_price, `ds`.status, `ds`.payment_method, `ds`.tax_rate, `ds`.payee, `ds`.collection_account, `ds`.bank_name, `ds`.payment_time,c.name as company_name');
        return $data;
    }

    // 处理分销商结算申请
    public function handleDistributionSettlementApply($params)
    {
        try {
            if (empty($params['id'])) {
                return ['message' => Error::REQUEST_PARAMETER_EXCEPTION];
            }

            $info = $this->distributionSettlementModel->getInfoByWhere(['id' => $params['id']], 'status');
            if (empty($info) || $info['status'] != 2) {
                return ['message' => Error::REQUEST_PARAMETER_EXCEPTION];
            }

            $bool = $this->distributionSettlementModel->edit(['id' => $params['id'], 'status' => 3, 'payment_time' => time()]);

            return true;
        } catch (\Exception $e) {
            return ['message' => $e->getMessage()];
        }
    }

    /**
     * 虚拟支付提现列表
     * @param $params
     * @return array
     */
    public function getVirtualWithdrawOrderList($params): array
    {
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 10;
        $where = [];

        // 公司搜索
        if (!empty($params['applet_id'])) {
            $where[] = ['applet_id', '=', $params['applet_id']];
        }

        $data = $this->withdrawOrderRecordsModel->getPagingDataByWhere($where, $page, $limit, 'id,applet_id,withdraw_no,withdraw_amount,status,wx_withdraw_no,fail_reason,operator_id,created_at,updated_at');
        foreach ($data['data'] as $key => $value) {
            $data['data'][$key]['status_text'] = WithdrawOrderRecords::STATUS_ARR[$value->status] ?? '提现中';
            $data['data'][$key]['applet'] = $value->applet ?? '';
            $data['data'][$key]['admin'] = $value->admin ?? '';
        }
        return $data;
    }

    /**
     * 编辑提现申请记录表
     * @param $params
     * @return bool
     */
    public function editWithdrawOrderRecords($params,$adminUserInfo=null): bool
    {
        $params['operator_id'] = $adminUserInfo['user_id'] ?? 0;
        $params['withdraw_no'] = WechatService::genOrderNo();
        $res = $this->createWithdrawOrder($params);
        $errcode = Arr::get($res,'errcode',-1);
        $errmsg = Arr::get($res,'errmsg','');
        if ($errcode == 0){
            $params['wx_withdraw_no'] = Arr::get($res,'wx_withdraw_no');
            $params['status'] = WithdrawOrderRecords::STATUS_APPLY_SUCCESS;
        }else{
            $params['status'] = WithdrawOrderRecords::STATUS_FAIL;
            $params['fail_reason'] = Arr::get($res,'fail_reason',$errmsg);
        }
        $id = $this->withdrawOrderRecordsModel->edit($params);
        if (!$id) {
            return false;
        }
        return true;
    }

    /**
     * pay_sig签名算法计算示例
     * @param $uri
     * @param $postBody
     * @param $appKey
     * @return string
     */
    public function calcPaySig($uri, $postBody, $appKey): string
    {
        $needSignMsg = $uri . '&' . $postBody;
        return hash_hmac('sha256', $needSignMsg, $appKey);
    }


    /**
     * 创建提现单
     * @param $params
     * @return mixed
     */
    public function createWithdrawOrder($params)
    {
        $uri = '/xpay/create_withdraw_order';
        $appletData = Applet::where('id',$params['applet_id'])->field('id, name, url, app_id, app_secret, sandbox_key, prod_key')->find();
        $appKey = config('app.env') == 'sandbox' ? $appletData->sandbox_key : $appletData->prod_key;
        // 获取微信accessToken
        $accessToken = Common::getWechatAppletAccessToken($appletData->app_id, $appletData->app_secret, $appletData->id);
        // 调用第三方请求参数
        $postBody =  json_encode([
            'withdraw_no' => $params['withdraw_no'],
            'withdraw_amount' => $params['withdraw_amount'] ?? '',
            'env' => config('app.env') == 'sandbox' ? 1 : 0,//0-正式环境 1-沙箱环境
        ]);
        flog([
            'message' => '创建提现单请求参数',
            'postBody' => $postBody,
        ],'wechat_pay');
        $postParams = [
            'access_token' => $accessToken,
            'pay_sig' => $this->calcPaySig($uri, $postBody,$appKey),
        ];
        $requestUrl = config('enum.wechat_pay_base_url').$uri.'?'.http_build_query($postParams);
        $result = Common::curlRequest($requestUrl, 'POST', $postBody);
        $res = json_decode($result, true);
        flog([
            'message' => '创建提现单返回参数',
            'res' => $res,
        ],'wechat_pay');
        return $res;
    }


    /**
     * 广告金列表
     * @param $params
     * @return 
     */
    public function getAdFunds($params)
    {
        // var_dump($params);exit;
        $page = $params['page'] ?? 1;
        $limit = $params['limit'] ?? 10;

        // 公司搜索
        if (!empty($params['company_id'])) {
            $where[] = ['company_id', '=', $params['company_id']];
        }

        // 小程序搜索
        if (!empty($params['applet_id'])) {
            $where[] = ['applet_id', '=', $params['applet_id']];
        } else {
            $where[] = ['applet_id', 'EXP', Db::raw('IS NOT NULL')];
        }

        // 结算日期搜索
        if (!empty($params['start_date'])) {
            $where[] = ['start_date', '>=', date('Ymd', strtotime($params['start_date']) - 3 * 86400)];
        }else{
            return ['total' => 0, 'total_page' => 0, 'data' => []];
        }
        if (!empty($params['end_date'])) {
            $where[] = ['end_date', '<=', date('Ymd', strtotime($params['end_date']) - 3 * 86400)];
        }else{
            return ['total' => 0, 'total_page' => 0, 'data' => []];
        }
        $totalAndPagingData=Db::name('ad_funds')->where($where)->count();
        $offset = ($page - 1) * $limit;
        $totalPage = ceil($totalAndPagingData / $limit);
        $data = Db::name('ad_funds')->where($where)->limit($offset, $limit)->order('start_date desc,applet_id asc')->select()->toArray();
        //返回格式
        foreach($data as $k=>$v){
            $data[$k]['start_query_date']=date('Y-m-d', strtotime($data[$k]['start_date']) + 3 * 86400);
            $data[$k]['end_query_date']=date('Y-m-d', strtotime($data[$k]['end_date']) + 3 * 86400);
        }
        return ['total' => $totalAndPagingData, 'total_page' => $totalPage, 'data' => $data];
    }

    //广告金列表导出
    public function exportAdFunds($params)
    {
        // 小程序搜索
        if (!empty($params['applet_id'])) {
            $where[] = ['applet_id', '=', $params['applet_id']];
        } else {
            $where[] = ['applet_id', 'EXP', Db::raw('IS NOT NULL')];
        }

        // 结算日期搜索
        if (!empty($params['start_date'])) {
            $where[] = ['start_date', '>=', date('Ymd', strtotime($params['start_date']) - 3 * 86400)];
        } else {
            echo '请选择时间'; exit();
        }

        if (!empty($params['end_date'])) {
            $where[] = ['end_date', '<=', date('Ymd', strtotime($params['end_date']) - 3 * 86400)];
        } else {
            echo '请选择时间'; exit();
        }

        $data = Db::name('ad_funds')->where($where)->order('start_date desc,applet_id asc')->select()->toArray();
        if (!$data) {
            echo '无数据'; exit();
        }

        set_time_limit(0);

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setCellValueByColumnAndRow(1, 1, '时间周期');
        $worksheet->setCellValueByColumnAndRow(2, 1, '结算周期');
        $worksheet->setCellValueByColumnAndRow(3, 1, '小程序');
        $worksheet->setCellValueByColumnAndRow(4, 1, '主体');
        $worksheet->setCellValueByColumnAndRow(5, 1, '充值');
        $worksheet->setCellValueByColumnAndRow(6, 1, '退款');
        $worksheet->setCellValueByColumnAndRow(7, 1, '腾迅广告激励金');

        $line = 2;
        foreach ($data as $item) {
            $start_query_date = date('Y-m-d', strtotime($item['start_date']) + 3 * 86400);
            $end_query_date = date('Y-m-d', strtotime($item['end_date']) + 3 * 86400);
            $worksheet->setCellValueByColumnAndRow(1, $line, $start_query_date .'至'.$end_query_date);
            $worksheet->setCellValueByColumnAndRow(2, $line, $item['start_date'] .'至'.$item['end_date']);
            $worksheet->setCellValueByColumnAndRow(3, $line, $item['applet_name']);
            $worksheet->setCellValueByColumnAndRow(4, $line, $item['company_name']);
            $worksheet->setCellValueByColumnAndRow(5, $line, $item['price']);
            $worksheet->setCellValueByColumnAndRow(6, $line, $item['refund_price']);
            $worksheet->setCellValueByColumnAndRow(7, $line, $item['tx_ad_price']);
            $line++;
        }

        $filename = $params['start_date'].'-'.$params['end_date'].'广告金导出列表.xlsx';

        ob_end_clean();
        ob_start();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 小程序列表
     * @param $params
     * @return 
     */
    public function getAppletList($params)
    {
        $data = Db::name('applet')->where(['status' => 1])->field('name, id')->select();
        return $data ? $data->toArray() : [];
    }
}